# @CreateTime : 2019/12/10 11:49
# @Author : may
# @Desc : 数据录入
import time

from selenium.webdriver.common.by import By
from base import Base
from logger import Logger
import config


class DataEntry(Base):
    """
    数据录入
    """

    logger = Logger('DataEntry').get_log()

    def click_data_entry(self):
        """点击数据录入并切换iframe"""

        # 刷新页面
        self.refresh()
        # 切换项目分期
        stage = config.get_cfg('project', 'project_name') + config.get_cfg('project', 'stage')
        self.click((By.CLASS_NAME, 'allItemsp'))
        self.click((By.LINK_TEXT, stage))
        time.sleep(1)
        self.click((By.CLASS_NAME, 'sjlr'))
        self.switch_to_data_entry()

    def switch_to_data_entry(self):
        """进入数据录入iframe"""
        self.switch_to_frame('sjlr')

    def switch_to_credentials(self):
        """进入各证件iframe"""
        self.switch_to_default_content()
        # 证件页面iframe
        loc_credentials = (By.XPATH, "//div[@class='layui-layer-content']/iframe[1]")
        iframe_credentials = self.find_element(loc_credentials)
        self.switch_to_frame(iframe_credentials)

    def switch_to_add(self):
        """进入添加iframe"""
        self.switch_to_default_content()
        iframe = self.find_element((By.XPATH, '(//div[@class="layui-layer-content"])[last()]/iframe[1]'))
        self.switch_to_frame(iframe)

    def select_credentials(self, option):
        """
        选择证件

        :param option: 数字，含义如下
            1：国有土地使用权出（受）让合同
            2：国有土地使用权证
            3：立项批文
            4：拆迁补偿协议及花名册
            5：建设用地规划许可证
            6：建设工程规划许可证
            7：建设工程施工许可证
            8：成本合同
            9：测绘报告
            10：商品房预（销）售许可证
            11：普通标准住房审核表
            12：决算报告
            13：验收报告
            14：项目工程合同结算单
            15：建设工程竣工规划验收合格证
            16：同期住房平均单价数据
        :type option: int
        """

        if isinstance(option, int) and 1 <= option <= 16:
            xpath = f'//li[{option}]/p[1]'
            self.click((By.XPATH, xpath))
            self.switch_to_credentials()
        else:
            raise ValueError('option范围: 1-16')
        time.sleep(1)

    def click_add_btn(self):
        """点击添加按钮"""
        self.click((By.LINK_TEXT, '添 加'))
        self.switch_to_add()

    def click_update_btn(self):
        """点击修改按钮"""
        self.click((By.XPATH, '//tr[1]//td[2]//div[1]//a[2]//span[1]'))
        self.switch_to_add()

    def click_save_btn(self):
        """点击保存按钮"""
        self.click((By.XPATH, "//a[@class='saveBlue rightHandel']"))

    def click_btn_of_certificate(self, certificate_no, btn_loc):
        """点击详情按钮"""

        self.click_data_entry()
        self.select_credentials(certificate_no)
        self.click(btn_loc)
        self.switch_to_add()

    def click_export_btn(self, type_='new'):
        """
        点击导出按钮,除立项批文与测绘报告按钮名称为导出外，其他证件都为导出excel

        :param type_: 按钮类型，new或old，new为导出excel，old为导出，默认new
        :type type_: str
        :return:
        """
        self.switch_to_credentials()
        if type_ == 'new':
            self.click((By.LINK_TEXT, '导出excel'))
        else:
            self.click((By.LINK_TEXT, '导 出'))

    def get_info(self, xmid, contract_no):
        conn = self.get_conn()
        cursor = conn.cursor()
        with conn:
            cursor.execute("""
                            SELECT t.Dzjgh,
                                   t.Htbh,
                                   (SELECT a.FS_MC FROM ZDqdfs a WHERE a.FS_DM = t.Tdqdfs),
                                   t.Htjf,
                                   t.Htyf,
                                   t.Ydpzwh,
                                   t.Xmdz,
                                   t.Htqdrq,
                                   t.tdjfrq,
                                   CONVERT(DECIMAL(18, 2), t.Mj),
                                   CONVERT(DECIMAL(18, 2), t.Zdj),
                                   CONVERT(DECIMAL(18, 2), t.Rjl),
                                   CONVERT(DECIMAL(18, 2), t.Htje),
                                   CONVERT(DECIMAL(18, 2), t.lhl)
                            FROM Htqd t WHERE t.Xmid = ? AND t.Htbh = ?
                        """, (xmid, contract_no))
            row = list(cursor.fetchone())
            row[9] = str(row[9])
            row[10] = str(row[10])
            row[11] = str(row[11])
            row[12] = str(row[12])
            row[13] = str(row[13])
            row.sort()
        return row

    def get_certificate_guid(self, xmid, contract_no):
        conn = self.get_conn()
        cursor = conn.cursor()
        with conn:
            cursor.execute('SELECT t.ZsGuid FROM Htqd t WHERE t.Xmid = ? AND t.Htbh = ?', (xmid, contract_no))
            row = cursor.fetchone()
        return row[0]

    def get_contract_count(self, option, xmid):
        """查询证件数量"""

        conn = self.get_conn()
        cursor = conn.cursor()
        table = {1: 'Htqd', 2: 'DJgytdsyz', 3: 'djlxpw'}
        sql = f'SELECT COUNT(*) FROM {table[option]} t WHERE t.Xmid = ?'
        with conn:
            cursor.execute(sql, xmid)
            count = cursor.fetchone()[0]
        return count

    def get_info_a(self, xmid):
        conn = self.get_conn()
        cursor = conn.cursor()
        with conn:
            cursor.execute("""
                            SELECT
                            t.Dzjgh,
                            t.Htbh,
                            ( SELECT a.FS_MC FROM ZDqdfs a WHERE a.FS_DM = t.Tdqdfs ),
                            t.Htjf,
                            t.Htyf,
                            t.Ydpzwh,
                            t.Xmdz,
                            t.Htqdrq,
                            t.tdjfrq,
                            CONVERT ( DECIMAL ( 18, 2 ), t.Mj ),
                            CONVERT ( DECIMAL ( 18, 2 ), t.Zdj ),
                            CONVERT ( DECIMAL ( 18, 2 ), t.Rjl ),
                            CONVERT ( DECIMAL ( 18, 2 ), t.Htje ), 
                            CONVERT ( DECIMAL ( 18, 2 ), t.lhl ) 
                            FROM Htqd t WHERE t.Xmid = ?
                        """, xmid)
            rows = cursor.fetchall()
        return rows

    def get_info_b(self, project_id):
        conn = self.get_conn()
        cursor = conn.cursor()
        with conn:
            cursor.execute(
                """
                    SELECT t.Fzrq,
                           t.Zsbh,
                           convert(DECIMAL(18, 2), t.Syqmj),
                           convert(DECIMAL(18, 2), t.Bqxmsyqmj),
                           t.Dh,
                           (SELECT a.FS_MC FROM ZDqdfs a WHERE a.FS_DM = t.Qdfs),
                           t.Tdyt,
                           (SELECT b.LX_MC FROM ZDtdlx b WHERE b.LX_DM = t.Tdlx),
                           t.Ht_Guid
                    FROM DJgytdsyz t
                    WHERE t.Xmid = ?           
                """, project_id)
            rows = cursor.fetchall()
            data = []
            for row in rows:
                row = list(row)
                id_list = row[len(row) - 1].split(';')
                ids = []
                area = 0
                for id_ in id_list:
                    with conn:
                        cursor.execute('SELECT t.Htbh, convert(DECIMAL(18, 2), t.Mj) FROM Htqd t WHERE t.Xmid = ? AND '
                                       't.ZsGuid = ?', project_id, id_)
                        res = cursor.fetchone()
                        ids.append(res[0])
                        area += res[1]
                row.append(','.join(ids))
                row.append(area)
                row.pop(len(row) - 3)
                row[2] = str(row[2])
                row[3] = str(row[3])
                row[len(row) - 1] = str(row[len(row) - 1])
                data.append(row)
        return data

    def find_all(self, project_id):
        """查询所有立项批文信息"""

        conn = self.get_conn()
        cursor = conn.cursor()
        with conn:
            cursor.execute(
                """
                SELECT t.Fzrq,
                       t.Pwbh,
                       convert(VARCHAR(18), convert(DECIMAL(18, 2), t.Jsgm_nr)),
                       convert(VARCHAR(18), convert(DECIMAL(18, 4), t.Jhztz)),
                       convert(VARCHAR(18), convert(DECIMAL(18, 2), t.Bqydmj))
                FROM djlxpw t
                WHERE t.Xmid = ?
                """, project_id)
            row = list(cursor.fetchall())
            row.sort()
        return row
